package com.ontheroad.db.utils;

import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Iterator;
import java.util.List;
import java.util.Properties;

import org.apache.commons.lang.StringUtils;

import com.ontheroad.db.dto.ColumnInfoDto;
import com.ontheroad.db.dto.GDAODetailInfoDto;

@SuppressWarnings("rawtypes")
public class CopyOfGDAOToolsUtil {

	public static String getListDataByIndex(List<String> list, int index){
		if(list != null && list.size() > index){
			return list.get(index);
		}
		return "";
	}
	
	/**
	 * 获取db连接
	 * 
	 * @param configProperties
	 * @return
	 * @throws Exception
	 */
	public static Connection getConnection(String subLibPrex, Properties configProperties)
			throws Exception {
		String db_driver = configProperties.getProperty(subLibPrex + "DB_DRIVER");
		String db_url = configProperties.getProperty(subLibPrex + "DB_URL");
		String db_user = configProperties.getProperty(subLibPrex + "DB_USER");
		String db_password = configProperties.getProperty(subLibPrex + "DB_PASSWORD");
		try {
			Class.forName(db_driver);
			return DriverManager.getConnection(db_url, db_user, db_password);
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		}
	}

	/**
	 * 读取配置文件propertiesFilePath
	 * 
	 * @param propertiesFilePath
	 * @return
	 * @throws Exception
	 */
	public static Properties readProperties(String propertiesFilePath)
			throws Exception {
		Properties properties = new Properties();
		FileInputStream inStream = null;
		try {
			  inStream = new FileInputStream(propertiesFilePath);
			properties.load(inStream);
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		}finally{
			if(inStream != null){
				try {
					inStream.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
		return properties;
	}

	/**
	 * 读取流inputStream的信息
	 * 
	 * @param inputStream
	 * @return
	 * @throws Exception
	 */
	public static Properties readPropertiesByInputStream(InputStream inputStream)
			throws Exception {
		Properties properties = new Properties();
		try {
			properties.load(inputStream);
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		}
		return properties;

	}
	
	/**
	 * 判断字符串str是否为空
	 * 
	 * @param str
	 * @return
	 */
	public static boolean isEmpty(String str){
		if(str == null || str.trim().length() == 0){
			return true;
		}else{
			return false;
		}
	}
	
	/**
	 * 获取生成DAO的详细信息 （字段，属性，类型）
	 * 
	 * @param conn
	 * @param tableName
	 * @param sqlTypeProperties
	 * @return
	 * @throws Exception
	 */
	public static GDAODetailInfoDto getGDAODetailInfo(Connection conn, String tableName, String tableSeq, Properties sqlTypeProperties) throws Exception {
		
		GDAODetailInfoDto detailInfoDto = new GDAODetailInfoDto();
		detailInfoDto.setTableName(tableName);
		detailInfoDto.setClassName(getClassName(tableName));
		
		String seqIdName = getSEQID(conn, detailInfoDto, tableSeq);
		
		if(seqIdName == null){
			throw new Exception("表" + tableName + "的序列号无法获取");
		}
		
		detailInfoDto.setSeqIdName(seqIdName);
		
		final String executeSql = "select * from PURCHASE." + tableName;
		
		Statement statement = conn.createStatement();
		ResultSet resultSet = statement.executeQuery(executeSql);
		
		ResultSetMetaData metaData = resultSet.getMetaData();
		boolean pk_id = false;
		for(int i = 1 ; i <= metaData.getColumnCount() ; i++){
			ColumnInfoDto columnIDto = new ColumnInfoDto();
			columnIDto.setColumnName(metaData.getColumnName(i));
			columnIDto.setPropertyName(getPropertyName(metaData.getColumnName(i)));

			if(metaData.getColumnType(i) == 2){
				//System.out.println(tableName + " : " + columnIDto.getColumnName() + " : " + metaData.getScale(i) + " : " + metaData.getPrecision(i));
				if(metaData.getScale(i) > 0){
					columnIDto.setSqlType(7);
					columnIDto.setJavaType(getColumnMethod(sqlTypeProperties, 7));
				}else if(metaData.getPrecision(i) > 10){
					columnIDto.setSqlType(-5);
					columnIDto.setJavaType(getColumnMethod(sqlTypeProperties, -5));
				}else{
					columnIDto.setSqlType(4);
					columnIDto.setJavaType(getColumnMethod(sqlTypeProperties, 4));
				}
			}else{
				columnIDto.setSqlType(metaData.getColumnType(i));
				columnIDto.setJavaType(getColumnMethod(sqlTypeProperties, metaData.getColumnType(i)));
			}
			
			detailInfoDto.getColumnList().add(columnIDto);
			
//			System.out.println(tableName + " : " + columnIDto.getColumnName() + " : " + metaData.getScale(i) + " : " + columnIDto.getSqlType());
			
			String className = getColumnClassName(sqlTypeProperties, columnIDto.getSqlType());
			if(! detailInfoDto.getPackageNameList().contains(className) && !className.contains("lang")){
				detailInfoDto.getPackageNameList().add(className);
			}
			//判断表是否存在ID主键
			if(columnIDto.getPropertyName().equals("id")){
				pk_id = true;
			}
		}
		
		//主键不为ID的话，不能生成代码
		if(! pk_id){
			throw new Exception("表" + tableName + "的主键不为ID，无法自动生成代码");
		}
		
		return detailInfoDto;
	
	}
	
	
	/**
	 * 根据列名获取属性名
	 * 
	 * @param columnName
	 * @return
	 */
	public static String getPropertyName(String columnName){
		StringBuffer propertyName = new StringBuffer();
		columnName = columnName.toLowerCase();
		
		String[] array = columnName.split("_");
		
		propertyName.append(array[0]);
		
		for(int i = 1 ; i < array.length ; i++ ){
			if(StringUtils.isEmpty(array[i])){
				continue;
			}
//			propertyName += array[i].substring(0, 1).toUpperCase() + array[i].substring(1);
			propertyName.append( array[i].substring(0, 1).toUpperCase()).append( array[i].substring(1));
		}
		return propertyName.toString();
	}
	
	/**
	 * 根据表名获取类名
	 * 
	 * @param tableName
	 * @return
	 */
	public static String getClassName(String tableName){
		StringBuffer propertyName = new StringBuffer();
		tableName = tableName.toLowerCase();
		
		String[] array = tableName.split("_");
		
		for(int i = 0 ; i < array.length ; i++ ){
//			propertyName += array[i].substring(0, 1).toUpperCase() + array[i].substring(1);
			propertyName.append( array[i].substring(0, 1).toUpperCase()).append( array[i].substring(1));
		}
		return propertyName.toString();
	}
	
	/**
	 * 获取列对应java的类名（包名 + 类名）
	 * 
	 * @param sqlTypeProperties
	 * @param type
	 * @return
	 */
	public static String getColumnClassName(Properties sqlTypeProperties, int type) {

		String columnClassName = "";
		
		Iterator iterator = sqlTypeProperties.keySet().iterator();
		
		String key = null;
		while(iterator.hasNext()){
			key = (String) iterator.next();
			
			if(String.valueOf(type).equals(key)){
				String value = sqlTypeProperties.getProperty(key);
				
				String[] arrayStr = value.split(",");
				
				if(arrayStr != null && arrayStr.length > 1){
					if(arrayStr[1].indexOf(".") != -1){
						columnClassName = arrayStr[1].trim();
					}
				}
				break;
			}
		}
		
		return columnClassName;
		
	}
	
	/**
	 * 获取列对应java的类名（类名）
	 * 
	 * @param sqlTypeProperties
	 * @param type
	 * @return
	 */
	public static String getColumnMethod(Properties sqlTypeProperties, int type) {

		String columnMethod = null;
		
		Iterator iterator = sqlTypeProperties.keySet().iterator();
		
		String key = null;
		while(iterator.hasNext()){
			key = (String) iterator.next();
			
			if(String.valueOf(type).equals(key)){
				String value = sqlTypeProperties.getProperty(key);
				
				String[] arrayStr = value.split(",");
				
				if(arrayStr != null && arrayStr.length > 0){
					columnMethod = arrayStr[0].trim();
				}
				break;
			}
		}
		
		if(isEmpty(columnMethod)){
			columnMethod = "String";
		}
		
		return columnMethod;
		
	}
	
	
	/**
	 * 判断并返回表的sql id
	 * 
	 * @param conn
	 * @param detailInfoDto
	 * @return
	 */
	private static String getSEQID(Connection conn, GDAODetailInfoDto detailInfoDto, String tableSeq){
		
		if(!CopyOfGDAOToolsUtil.isEmpty(tableSeq)){
			try {
				executeSql(conn, "select PURCHASE." + tableSeq + ".NEXTVAL from DUAL");
				return tableSeq;
			} catch (SQLException e) {
				//e.printStackTrace();
			}
		}
		
		String seqID = "SEQ_" + detailInfoDto.getTableName() + "_ID";
		try {
			executeSql(conn, "select PURCHASE." + seqID + ".NEXTVAL from DUAL");
			return seqID;
		} catch (SQLException e) {
			//e.printStackTrace();
		}
		
		seqID = "SEQ_" + detailInfoDto.getClassName().toUpperCase() + "_ID";
		try {
			executeSql(conn, "select PURCHASE." + seqID + ".NEXTVAL from DUAL");
			return seqID;
		} catch (SQLException e1) {
			//e1.printStackTrace();
		}
		return null;
	}
	
	/**
	 * 执行sql
	 * 
	 * @param conn
	 * @param sql
	 * @throws SQLException
	 */
	private static void executeSql(Connection conn, String sql) throws SQLException{
		Statement statement = null;
		try {
			statement = conn.createStatement();
			statement.execute(sql);
		} catch (SQLException e) {
			throw e;
		}finally{
			if(statement!=null){
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
					throw e;
				}
			}
		}
	}
	
	
	
	
	
}
